import random
import re
from flask import Flask, render_template, request, redirect, url_for, session, flash
import pymysql
from pymysql.cursors import DictCursor
from decimal import Decimal
import datetime
from datetime import datetime

app = Flask(__name__)
app.secret_key = 'bank_system_secret_key'
app.config['DEBUG'] = True

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'jn050424',
    'db': 'kechengsheji',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}

def get_db_connection():
    return pymysql.connect(**DB_CONFIG)

# ------------------ 通用功能 ------------------
@app.route('/')
def index():
    if 'user_id' in session:
        return redirect(url_for('admin_dashboard' if session['user_type'] == 'admin' else 'customer_dashboard'))
    return render_template('login.html')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        user_type = request.form['user_type']

        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                if user_type == 'admin':
                    cursor.execute("SELECT * FROM admins WHERE username = %s AND password = %s",
                                 (username, password))
                    admin = cursor.fetchone()
                    if admin:
                        session['user_id'] = admin['admin_id']
                        session['user_type'] = 'admin'
                        session['username'] = admin['username']
                        return redirect(url_for('admin_dashboard'))
                else:
                    cursor.execute("""
                        SELECT ui.*, ci.cardID, ci.balance, ci.IsReportLoss
                        FROM userInfo ui 
                        JOIN cardInfo ci ON ui.customerID = ci.customerID
                        WHERE ci.cardID = %s AND ci.pass = %s
                    """, (username, password))
                    customer = cursor.fetchone()
                    if customer:
                        session['user_id'] = customer['customerID']
                        session['user_type'] = 'customer'
                        session['username'] = customer['customerName']
                        session['card_id'] = customer['cardID']
                        return redirect(url_for('customer_dashboard'))

                flash('登录失败，请检查账号密码', 'error')
    return render_template('login.html')

@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))

# ------------------ 管理员功能 ------------------

@app.route('/admin/customers')
def admin_customers():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    # 获取查询参数
    search_type = request.args.get('search_type', '')
    search_field = request.args.get('search_field', '')
    search_value = request.args.get('search_value', '')
    min_value = request.args.get('min_value', '')
    max_value = request.args.get('max_value', '')

    # 分页参数
    page = request.args.get('page', 1, type=int)
    per_page = 10  # 每页显示10条记录

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            # 基础查询
            sql = """
                SELECT 
                    ui.customerID,
                    ui.customerName,
                    ui.PID,
                    ui.telephone,
                    ui.address,
                    COUNT(ci.cardID) as card_count 
                FROM userInfo ui 
                LEFT JOIN cardInfo ci ON ui.customerID = ci.customerID
                GROUP BY ui.customerID
                HAVING 1=1
            """
            count_sql = "SELECT COUNT(DISTINCT ui.customerID) as total FROM userInfo ui LEFT JOIN cardInfo ci ON ui.customerID = ci.customerID WHERE 1=1"
            params = []
            count_params = []


            # 获取总数
            cursor.execute(count_sql, count_params)
            total = cursor.fetchone()['total']

            # 添加排序和分页
            sql += " ORDER BY ui.customerID LIMIT %s OFFSET %s"
            params.extend([per_page, (page - 1) * per_page])

            cursor.execute(sql, params)
            customers = cursor.fetchall()

    # 计算分页信息
    pagination = {
        'page': page,
        'per_page': per_page,
        'total': total,
        'pages': (total + per_page - 1) // per_page,
        'has_prev': page > 1,
        'has_next': page * per_page < total,
        'prev_num': page - 1 if page > 1 else None,
        'next_num': page + 1 if page * per_page < total else None
    }

    return render_template('admin/customers.html',
                           customers=customers,
                           pagination=pagination,
                           search_type=search_type,
                           search_field=search_field,
                           search_value=search_value,
                           min_value=min_value,
                           max_value=max_value)

@app.route('/admin/dashboard')
def admin_dashboard():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT COUNT(*) as customer_count FROM userInfo")
            customer_count = cursor.fetchone()['customer_count']

            cursor.execute("SELECT COUNT(*) as card_count FROM cardInfo")
            card_count = cursor.fetchone()['card_count']

            cursor.execute("SELECT SUM(balance) as total_balance FROM cardInfo")
            total_balance = cursor.fetchone()['total_balance'] or 0

            cursor.execute("""
                SELECT t.*, u.customerName 
                FROM tradeInfo t
                JOIN cardInfo c ON t.cardID = c.cardID
                JOIN userInfo u ON c.customerID = u.customerID
                ORDER BY t.tradeDate DESC LIMIT 10
            """)
            recent_transactions = cursor.fetchall()

    return render_template('admin/dashboard.html',
                         customer_count=customer_count,
                         card_count=card_count,
                         total_balance=total_balance,
                         transactions=recent_transactions)


@app.route('/admin/customers/add', methods=['GET', 'POST'])
def admin_add_customer():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    if request.method == 'POST':
        customer_name = request.form.get('customer_name', '').strip()
        id_card = request.form.get('id_card', '').strip()
        phone = request.form.get('phone', '').strip()
        address = request.form.get('address', '').strip()

        # 数据验证逻辑保持不变...

        try:
            with get_db_connection() as conn:
                with conn.cursor() as cursor:
                    # 检查身份证是否已存在
                    cursor.execute("SELECT customerID FROM userInfo WHERE PID = %s", (id_card,))
                    if cursor.fetchone():
                        flash('该身份证号已存在', 'error')
                        return render_template('admin/add_customer.html',
                                               form_data=request.form)

                    # 插入新客户（触发器会自动创建银行卡）
                    cursor.execute("""
                        INSERT INTO userInfo 
                        (customerName, PID, telephone, address) 
                        VALUES (%s, %s, %s, %s)
                    """, (customer_name, id_card.upper(), phone, address))
                    conn.commit()

                    # 获取新创建的客户ID
                    cursor.execute("SELECT customerID FROM userInfo WHERE PID = %s", (id_card,))
                    new_customer = cursor.fetchone()

                    # 查询自动创建的银行卡
                    cursor.execute("SELECT cardID FROM cardInfo WHERE customerID = %s", (new_customer['customerID'],))
                    new_card = cursor.fetchone()

                    flash(f'客户添加成功！自动创建的银行卡号：{new_card["cardID"]}', 'success')
                    return redirect(url_for('admin_customers'))

        except Exception as e:
            conn.rollback()
            flash(f'添加失败: {str(e)}', 'error')
            app.logger.error(f"添加客户失败: {str(e)}")
            return render_template('admin/add_customer.html',
                                   form_data=request.form)

    return render_template('admin/add_customer.html')


@app.route('/admin/customers/edit/<int:customer_id>', methods=['GET', 'POST'])
def admin_edit_customer(customer_id):
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            if request.method == 'POST':
                customer_name = request.form['customer_name']
                telephone = request.form['phone']
                address = request.form['address']

                try:
                    cursor.execute("""
                        UPDATE userInfo 
                        SET customerName=%s, telephone=%s, address=%s
                        WHERE customerID=%s
                    """, (customer_name, telephone, address, customer_id))
                    conn.commit()
                    flash('客户信息更新成功', 'success')
                    return redirect(url_for('admin_customers'))
                except Exception as e:
                    flash(f'更新失败: {str(e)}', 'error')

            cursor.execute("SELECT * FROM userInfo WHERE customerID=%s", (customer_id,))
            customer = cursor.fetchone()

    return render_template('admin/edit_customer.html', customer=customer)


@app.route('/admin/customers/delete/<int:customer_id>')
def admin_delete_customer(customer_id):
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    try:
        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                # 先删除关联的银行卡
                cursor.execute("DELETE FROM cardInfo WHERE customerID=%s", (customer_id,))
                # 再删除客户
                cursor.execute("DELETE FROM userInfo WHERE customerID=%s", (customer_id,))
                conn.commit()
        flash('客户删除成功', 'success')
    except Exception as e:
        flash(f'删除失败: {str(e)}', 'error')

    return redirect(url_for('admin_customers'))


@app.route('/admin/cards')
def admin_cards():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    # 获取查询参数
    search_type = request.args.get('search_type', '')
    search_field = request.args.get('search_field', '')
    search_value = request.args.get('search_value', '')
    min_value = request.args.get('min_value', '')
    max_value = request.args.get('max_value', '')

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            sql = """
                SELECT ci.*, ui.customerName, de.savingName 
                FROM cardInfo ci
                JOIN userInfo ui ON ci.customerID = ui.customerID
                JOIN deposit de ON ci.savingID = de.savingID
                WHERE 1=1
            """
            params = []

            # 构建查询条件
            if search_type and search_field:
                if search_type == 'exact':
                    if search_field == 'card_number' and search_value:
                        sql += " AND ci.cardID = %s"
                        params.append(search_value)
                    elif search_field == 'customer_id' and search_value:
                        sql += " AND ui.customerID = %s"
                        params.append(int(search_value))
                    elif search_field == 'balance' and search_value:
                        sql += " AND ci.balance = %s"
                        params.append(float(search_value))

                elif search_type == 'fuzzy':
                    if search_field == 'card_number' and search_value:
                        sql += " AND ci.cardID LIKE %s"
                        params.append(f"%{search_value}%")
                    elif search_field == 'customer_id' and search_value:
                        sql += " AND ui.customerName LIKE %s"
                        params.append(f"%{search_value}%")

                elif search_type == 'range' and search_field == 'balance':
                    if min_value:
                        sql += " AND ci.balance >= %s"
                        params.append(float(min_value))
                    if max_value:
                        sql += " AND ci.balance <= %s"
                        params.append(float(max_value))


            cursor.execute(sql, params)
            cards = cursor.fetchall()

    return render_template('admin/cards.html',
                           cards=cards,
                           search_type=search_type,
                           search_field=search_field,
                           search_value=search_value,
                           min_value=min_value,
                           max_value=max_value)

# 解挂功能
@app.route('/admin/cards/unlock/<string:card_id>')
def admin_unlock_card(card_id):
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    try:
        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute("""
                    UPDATE cardInfo 
                    SET IsReportLoss = '否'  # 修改为文字
                    WHERE cardID = %s
                """, (card_id,))
                conn.commit()
        flash('银行卡解挂成功', 'success')
    except Exception as e:
        flash(f'解挂失败: {str(e)}', 'error')

    return redirect(url_for('admin_cards'))


@app.route('/admin/cards/add', methods=['GET', 'POST'])
def admin_add_card():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            if request.method == 'POST':
                # 自动生成符合规范的卡号（前8位10103576 + 后8位随机）
                card_id = '10103576' + ''.join(random.choice('0123456789') for _ in range(8))
                customer_id = request.form['customer_id']
                saving_id = request.form['saving_id']
                password = request.form.get('password', '888888')  # 默认密码888888
                open_amount = Decimal(request.form['open_amount'])

                # 验证开户金额
                if open_amount < 1:
                    flash('开户金额不能小于1元', 'error')
                    return redirect(url_for('admin_add_card'))

                try:
                    # 插入新卡记录
                    cursor.execute("""
                        INSERT INTO cardInfo 
                        (cardID, pass, customerID, savingID, openDate, openMoney, balance, curID, IsReportLoss)
                        VALUES (%s, %s, %s, %s, CURDATE(), %s, %s, 'RMB', '否')
                    """, (card_id, password, customer_id, saving_id, open_amount, open_amount))
                    conn.commit()

                    # 记录开户交易
                    cursor.execute("""
                        INSERT INTO tradeInfo 
                        (tradeDate, tradeType, cardID, tradeMoney, remark)
                        VALUES (NOW(), '开户存款', %s, %s, '初始开户')
                    """, (card_id, open_amount))
                    conn.commit()

                    flash(f'银行卡开户成功！卡号：{card_id}', 'success')
                    return redirect(url_for('admin_cards'))
                except Exception as e:
                    conn.rollback()
                    flash(f'开户失败: {str(e)}', 'error')

            # 获取客户和存款类型列表
            cursor.execute("SELECT customerID, customerName FROM userInfo")
            customers = cursor.fetchall()
            cursor.execute("SELECT savingID, savingName FROM deposit")
            deposit_types = cursor.fetchall()

    return render_template('admin/add_card.html',
                           customers=customers,
                           deposit_types=deposit_types)


@app.route('/admin/cards/edit/<string:card_id>', methods=['GET', 'POST'])
def admin_edit_card(card_id):
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            if request.method == 'POST':
                customer_id = request.form['customer_id']
                saving_id = request.form['saving_id']
                password = request.form['password']
                balance = request.form['balance']
                is_report_loss = request.form.get('is_report_loss', '否')

                try:
                    cursor.execute("""
                        UPDATE cardInfo 
                        SET customerID=%s, savingID=%s, pass=%s, 
                            balance=%s, IsReportLoss=%s
                        WHERE cardID=%s
                    """, (customer_id, saving_id, password, balance, is_report_loss, card_id))
                    conn.commit()
                    flash('银行卡信息更新成功', 'success')
                    return redirect(url_for('admin_cards'))
                except Exception as e:
                    flash(f'更新失败: {str(e)}', 'error')

            # 获取当前银行卡信息
            cursor.execute("SELECT * FROM cardInfo WHERE cardID=%s", (card_id,))
            card = cursor.fetchone()

            # 获取客户列表和存款类型
            cursor.execute("SELECT customerID, customerName FROM userInfo")
            customers = cursor.fetchall()
            cursor.execute("SELECT savingID, savingName FROM deposit")
            deposit_types = cursor.fetchall()

    return render_template('admin/edit_card.html',
                           card=card,
                           customers=customers,
                           deposit_types=deposit_types)

@app.route('/admin/cards/delete/<string:card_id>')
def admin_delete_card(card_id):
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    try:
        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute("DELETE FROM cardInfo WHERE cardID=%s", (card_id,))
                conn.commit()
        flash('银行卡删除成功', 'success')
    except Exception as e:
        flash(f'删除失败: {str(e)}', 'error')

    return redirect(url_for('admin_cards'))

# 存款业务管理
@app.route('/admin/deposit_types')
def admin_deposit_types():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    search = request.args.get('search', '')

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            sql = "SELECT savingID, savingName, descrip FROM deposit"
            params = []

            if search:
                sql += " WHERE savingName LIKE %s OR descrip LIKE %s"
                params.extend([f"%{search}%", f"%{search}%"])

            cursor.execute(sql, params)
            deposit_types = cursor.fetchall()

    return render_template('admin/deposit_types.html',
                           deposit_types=deposit_types,
                           search=search)

@app.route('/admin/deposit_types/add', methods=['GET', 'POST'])
def admin_add_deposit_type():
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    if request.method == 'POST':
        saving_name = request.form['saving_name']
        description = request.form.get('descrip', '')

        try:
            with get_db_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute("""
                        INSERT INTO deposit (savingName, descrip)
                        VALUES (%s,  %s)
                    """, (saving_name, description))
                    conn.commit()
            flash('存款类型添加成功', 'success')
            return redirect(url_for('admin_deposit_types'))
        except Exception as e:
            flash(f'添加失败: {str(e)}', 'error')

    return render_template('admin/add_deposit_type.html')

@app.route('/admin/deposit_types/edit/<int:saving_id>', methods=['GET', 'POST'])
def admin_edit_deposit_type(saving_id):
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            if request.method == 'POST':
                saving_name = request.form['saving_name']
                description = request.form.get('descrip', '')

                try:
                    cursor.execute("""
                        UPDATE deposit 
                        SET savingName=%s, descrip=%s
                        WHERE savingID=%s
                    """, (saving_name, description, saving_id))
                    conn.commit()
                    flash('存款类型更新成功', 'success')
                    return redirect(url_for('admin_deposit_types'))
                except Exception as e:
                    conn.rollback()
                    flash(f'更新失败: {str(e)}', 'error')

            cursor.execute("SELECT * FROM deposit WHERE savingID=%s", (saving_id,))
            deposit_type = cursor.fetchone()

    return render_template('admin/edit_deposit_type.html', deposit_type=deposit_type)

@app.route('/admin/deposit_types/delete/<int:saving_id>')
def admin_delete_deposit_type(saving_id):
    if session.get('user_type') != 'admin':
        return redirect(url_for('login'))

    try:
        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                # 检查是否有银行卡使用该存款类型
                cursor.execute("SELECT COUNT(*) FROM cardInfo WHERE savingID=%s", (saving_id,))
                count = cursor.fetchone()['COUNT(*)']
                if count > 0:
                    flash('该存款类型已有银行卡使用，不能删除', 'error')
                else:
                    cursor.execute("DELETE FROM deposit WHERE savingID=%s", (saving_id,))
                    conn.commit()
                    flash('存款类型删除成功', 'success')
    except Exception as e:
        flash(f'删除失败: {str(e)}', 'error')

    return redirect(url_for('admin_deposit_types'))

"客户功能"
@app.route('/customer/dashboard')
def customer_dashboard():
    if session.get('user_type') != 'customer':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM userInfo WHERE customerID = %s", (session['user_id'],))
            customer = cursor.fetchone()

            cursor.execute("""
            SELECT ci.cardID, ci.balance,
                  CASE WHEN ci.IsReportLoss = '是' THEN '是' 
                       WHEN ci.IsReportLoss = 1 THEN '是'
                       ELSE '否' END AS IsReportLoss,
                  de.savingName 
            FROM cardInfo ci
            JOIN deposit de ON ci.savingID = de.savingID
            WHERE ci.customerID = %s
            """, (session['user_id'],))
            cards = cursor.fetchall()

            cursor.execute("""
                SELECT * FROM tradeInfo 
                WHERE cardID = %s 
                ORDER BY tradeDate DESC LIMIT 10
            """, (session.get('card_id'),))
            transactions = cursor.fetchall()

    return render_template('customer/dashboard.html',
                         customer=customer,
                         cards=cards,
                         transactions=transactions)

# 挂失功能
@app.route('/customer/report_loss', methods=['GET', 'POST'])
def report_loss():
    if session.get('user_type') != 'customer':
        return redirect(url_for('login'))

    if request.method == 'POST':
        card_id = request.form['card_id']
        password = request.form['password']

        with get_db_connection() as conn:
            with conn.cursor() as cursor:
                # 检查银行卡是否存在且属于当前用户
                cursor.execute("""
                    SELECT * FROM cardInfo 
                    WHERE cardID = %s AND pass = %s AND customerID = %s
                """, (card_id, password, session['user_id']))
                card = cursor.fetchone()

                if not card:
                    flash('银行卡号或密码错误', 'error')
                elif card['IsReportLoss'] == '是':
                    flash('该银行卡已挂失，无需重复操作', 'warning')
                else:
                    try:
                        # 更新挂失状态为"是"
                        cursor.execute("""
                            UPDATE cardInfo 
                            SET IsReportLoss = '是'
                            WHERE cardID = %s
                        """, (card_id,))
                        conn.commit()
                        flash('银行卡挂失成功', 'success')
                        return redirect(url_for('customer_dashboard'))
                    except Exception as e:
                        conn.rollback()
                        flash(f'挂失失败: {str(e)}', 'error')

    return render_template('customer/report_loss.html')

@app.route('/customer/change_password', methods=['GET', 'POST'])
def change_password():
    if session.get('user_type') != 'customer':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            if request.method == 'POST':
                card_id = request.form['card_id']
                old_password = request.form['old_password']
                new_password = request.form['new_password']
                confirm_password = request.form['confirm_password']

                if new_password != confirm_password:
                    flash('两次输入的新密码不一致', 'error')
                elif len(new_password) < 6:
                    flash('密码长度不能少于6位', 'error')
                else:
                    cursor.execute("""
                        SELECT * FROM cardInfo 
                        WHERE cardID = %s AND pass = %s AND customerID = %s
                    """, (card_id, old_password, session['user_id']))
                    card = cursor.fetchone()

                    if not card:
                        flash('原密码错误或卡号无效', 'error')
                    else:
                        cursor.execute("""
                            UPDATE cardInfo SET pass = %s 
                            WHERE cardID = %s
                        """, (new_password, card_id))
                        conn.commit()
                        flash('密码修改成功', 'success')
                        return redirect(url_for('customer_dashboard'))

            cursor.execute("""
                SELECT * FROM cardInfo 
                WHERE customerID = %s AND IsReportLoss = 0
            """, (session['user_id'],))
            cards = cursor.fetchall()

    return render_template('customer/change_password.html', cards=cards)

# 存款功能（添加挂失检查）
@app.route('/customer/deposit', methods=['GET', 'POST'])
def customer_deposit():
    if session.get('user_type') != 'customer':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT ci.cardID, ci.balance, ci.IsReportLoss, de.savingName 
                FROM cardInfo ci
                JOIN deposit de ON ci.savingID = de.savingID
                WHERE ci.customerID = %s
            """, (session['user_id'],))
            cards = cursor.fetchall()

            if request.method == 'POST':
                card_id = request.form['card_id']
                amount = request.form['amount']
                password = request.form['password']

                try:
                    amount = float(amount)
                    if amount <= 0:
                        flash('存款金额必须大于0', 'error')
                        return render_template('customer/deposit.html', cards=cards)

                    cursor.execute("""
                        SELECT * FROM cardInfo 
                        WHERE cardID = %s AND pass = %s AND customerID = %s
                    """, (card_id, password, session['user_id']))
                    card = cursor.fetchone()

                    if not card:
                        flash('银行卡号或密码错误', 'error')
                    elif card['IsReportLoss'] == '是':
                        flash('该银行卡已挂失，不能进行存款操作', 'error')
                    else:
                        conn.begin()
                        cursor.execute("""
                            UPDATE cardInfo 
                            SET balance = balance + %s 
                            WHERE cardID = %s
                        """, (amount, card_id))
                        cursor.execute("""
                            INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark)
                            VALUES (%s, '存款', %s, %s, '客户存款')
                        """, (datetime.now(), card_id, amount))
                        conn.commit()
                        flash(f'成功存入 {amount} 元', 'success')
                        return redirect(url_for('customer_dashboard'))

                except ValueError:
                    flash('请输入有效的金额', 'error')
                except Exception as e:
                    conn.rollback()
                    flash(f'存款失败: {str(e)}', 'error')

    return render_template('customer/deposit.html', cards=cards)

# 取款功能（添加挂失检查）
@app.route('/customer/withdraw', methods=['GET', 'POST'])
def customer_withdraw():
    if session.get('user_type') != 'customer':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT ci.cardID, ci.balance, ci.IsReportLoss, de.savingName 
                FROM cardInfo ci
                JOIN deposit de ON ci.savingID = de.savingID
                WHERE ci.customerID = %s
            """, (session['user_id'],))
            cards = cursor.fetchall()

            if request.method == 'POST':
                card_id = request.form['card_id']
                amount = request.form['amount']
                password = request.form['password']

                try:
                    amount = float(amount)
                    if amount <= 0:
                        flash('取款金额必须大于0', 'error')
                        return render_template('customer/withdraw.html', cards=cards)

                    cursor.execute("""
                        SELECT * FROM cardInfo 
                        WHERE cardID = %s AND pass = %s AND customerID = %s
                    """, (card_id, password, session['user_id']))
                    card = cursor.fetchone()

                    if not card:
                        flash('银行卡号或密码错误', 'error')
                    elif card['IsReportLoss'] == '是':
                        flash('该银行卡已挂失，不能进行取款操作', 'error')
                    elif card['balance'] < amount:
                        flash('余额不足，无法完成取款', 'error')
                    else:
                        conn.begin()
                        cursor.execute("""
                            UPDATE cardInfo 
                            SET balance = balance - %s 
                            WHERE cardID = %s
                        """, (amount, card_id))
                        cursor.execute("""
                            INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark)
                            VALUES (%s, '取款', %s, %s, '客户取款')
                        """, (datetime.now(), card_id, amount))
                        conn.commit()
                        flash(f'成功取出 {amount} 元', 'success')
                        return redirect(url_for('customer_dashboard'))

                except ValueError:
                    flash('请输入有效的金额', 'error')
                except Exception as e:
                    conn.rollback()
                    flash(f'取款失败: {str(e)}', 'error')

    return render_template('customer/withdraw.html', cards=cards)


# 转账功能（添加挂失检查）
@app.route('/customer/transfer', methods=['GET', 'POST'])
def customer_transfer():
    if session.get('user_type') != 'customer':
        return redirect(url_for('login'))

    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT ci.cardID, ci.balance, ci.IsReportLoss, de.savingName 
                FROM cardInfo ci
                JOIN deposit de ON ci.savingID = de.savingID
                WHERE ci.customerID = %s
            """, (session['user_id'],))
            cards = cursor.fetchall()

            if request.method == 'POST':
                from_card = request.form['from_card']
                to_card = request.form['to_card']
                amount = request.form['amount']
                password = request.form['password']
                remark = request.form.get('remark', '转账')

                try:
                    amount = float(amount)
                    if amount <= 0:
                        flash('转账金额必须大于0', 'error')
                        return render_template('customer/transfer.html', cards=cards)

                    # 验证转出卡
                    cursor.execute("""
                        SELECT * FROM cardInfo 
                        WHERE cardID = %s AND pass = %s AND customerID = %s
                    """, (from_card, password, session['user_id']))
                    from_account = cursor.fetchone()

                    if not from_account:
                        flash('转出卡号或密码错误', 'error')
                    elif from_account['IsReportLoss'] == '是':
                        flash('转出银行卡已挂失，不能进行转账', 'error')
                    else:
                        # 验证转入卡
                        cursor.execute("SELECT * FROM cardInfo WHERE cardID = %s", (to_card,))
                        to_account = cursor.fetchone()

                        if not to_account:
                            flash('转入卡号不存在', 'error')
                        elif from_account['balance'] < amount:
                            flash('余额不足，无法完成转账', 'error')
                        else:
                            conn.begin()
                            # 扣除转出卡金额
                            cursor.execute("""
                                UPDATE cardInfo 
                                SET balance = balance - %s 
                                WHERE cardID = %s
                            """, (amount, from_card))
                            # 增加转入卡金额
                            cursor.execute("""
                                UPDATE cardInfo 
                                SET balance = balance + %s 
                                WHERE cardID = %s
                            """, (amount, to_card))
                            # 记录交易
                            cursor.execute("""
                                INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark)
                                VALUES (%s, '转账支出', %s, %s, %s)
                            """, (datetime.now(), from_card, amount, f"转账至{to_card}: {remark}"))
                            cursor.execute("""
                                INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark)
                                VALUES (%s, '转账收入', %s, %s, %s)
                            """, (datetime.now(), to_card, amount, f"来自{from_card}的转账: {remark}"))
                            conn.commit()
                            flash(f'成功转账 {amount} 元至卡号 {to_card}', 'success')
                            return redirect(url_for('customer_dashboard'))

                except ValueError:
                    flash('请输入有效的金额', 'error')
                except Exception as e:
                    conn.rollback()
                    flash(f'转账失败: {str(e)}', 'error')

    return render_template('customer/transfer.html', cards=cards)

@app.context_processor
def inject_now():
    return {
        'now': datetime.now(),
        'current_year': datetime.now().year
    }

if __name__ == '__main__':
    app.run(host='127.0.0.1', port=5000, debug=True)